set hive.exec.dynamic.partition= true;
set hive.exec.dynamic.partition.mode= 'nonstrict';
set hive.exec.max.dynamic.partitions.pernode= 200;
set hive.exec.max.dynamic.partitions=200 ;
insert overwrite table jms_dm.dm_deliver_sign_hour_sum_dt partition (dt)
select
       max(virt_code) as virt_code  -- 虚拟代理区code
       ,max(virt_name) as virt_name  -- 虚拟代理区
       ,max(manage_code) as manage_code  -- 管理大区code
       ,max(manage_name) as manage_name  -- 管理大区
       ,max(agent_code) as agent_code  -- 代理区code
       ,max(agent_name) as agent_name  -- 代理区
       ,max(fran_code) as fran_code  -- 加盟商code
       ,max(fran_name) as fran_name  -- 加盟商
       ,max(provider_id) as provider_id  -- 省id
       ,max(provider_desc) as provider_desc  -- 省
       ,max(city_id) as city_id  -- 城市id
       ,max(city_desc) as city_desc  -- 城市
       ,max(area_id) as area_id  -- 区县id
       ,max(area_desc) as area_desc  -- 区县
       ,scan_site_code           --网点
       ,max(scan_site) as scan_site  --网点
       ,count(1) as dispatch_count     --  派件量,
       ,sum(if(sign_time is not null ,1,0)) as  onSign_count       -- 已签收量,
       ,sum(if(sign_time is  null ,1,0)) as unSign_count      --   未签收量,
       ,sum(if(difficult_first_type is not null,1,0)) as wrong_count       --   问题件,

       ,sum(if(sign_aging=0,1,0)) as one_day_sign      --  当日签收量,
       ,sum(if(sign_aging=1,1,0)) as two_day_sign      --   次日签收量,
       ,sum(if(sign_aging=2,1,0)) as three_day_sign    --    第三日签收量,
       ,sum(if(sign_aging>2,1,0)) as over_three_day_sign   --  三日后签收量,

       ,sum(if(waybill_source ='驿站',1,0)) as stage_count        --  入驿站量,
       ,sum(if(waybill_source ='柜机',1,0)) as cupboard_count     --  入快递柜量,
       ,sum(if(waybill_source ='快件签收',1,0)) as direct_sign_count  --   快件签收量,
       ,dt as date_time
     ,sum(if(difficult_first_type is not null and sign_time is not null ,1,0)) as wrong_count_sign       --   问题件签收
     ,sum(if(difficult_first_type is not null and sign_time is  null ,1,0)) as    wrong_count_onsign       --   问题件未签收
     ,`current_timestamp`() as current_time
       ,dt
from jms_dm.dm_deliver_sign_hour_detail_dt
where dt>=date_sub(substring('{{ execution_date | cst_hour }}',0,10),6)
and dt<=substring('{{ execution_date | cst_hour }}',0,10)
group by dt,scan_site_code
distribute by 20;
